set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 20;
set hive.exec.max.dynamic.partitions.pernode = 20;
set hive.merge.mapredfiles = true;
set hive.merge.mapfiles = true;
set hive.merge.size.per.task = 128000000;
set hive.merge.smallfiles.avgsize = 128000000;
-- -------------------------------------------------
--    Project Name:  场地数字化网点实际货量统计-明细数据
--    File Name:     jms_dm.dm_site_digital_real_detail_dt
--    Job Name:      jms_dm.dm_site_digital_real_detail_dt
--    Description:   根据运单实际轨迹生成网点到发明细
--    Author:        leichao
--    date:          2021-12-11 22:30
-- -------------------------------------------------
with t_route as (          --取当日和历史三天的到发数据
select *
from (
    SELECT
        waybill_no                                                                                                          -- 运单号
        ,send_station_number                                                             as step                            -- 站点序号
        ,send_station_network_code                                                       as network_code                    -- 站点编码
        ,lead(send_station_network_code) over(partition by waybill_no order by send_station_number desc) as prev_network_code   -- 上一站点编码
        ,nvl(send_station_loading_next_network_code,send_station_send_next_network_code) as loading_send_next_network_code  -- 站点最晚装车发件下一站站点编码
        ,nvl(send_station_arrival_early_time,send_station_unloading_early_time)          as arrival_early_scantime          -- 站点最早卸车到件时间
        ,nvl(send_station_send_later_time,send_station_loading_later_time)               as send_later_scantime             -- 站点最晚装车发件时间
        ,send_station_unloading_arrival_early_shipment_no                                as unloading_arrival_shipment_no   -- 站点卸车到件任务号
        ,send_station_shipment_no                                                        as loading_send_shipment_no        -- 站点装车发件任务号
    FROM jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-44)
    and '{{ execution_date | cst_ds }}'
    and send_station_network_type in ('2','3','4')
    and date_format(etl_load_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
) tmp
where (date_format(arrival_early_scantime,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
    or date_format(send_later_scantime,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}')),

t_network_final as (
select
    id
    ,code
    ,name
    ,type_id
    ,is_entrepot
    ,is_distribution_center
    ,provider_id
    ,provider_desc
    ,agent_id
    ,agent_code
    ,agent_name
    ,franchisee_id
    ,franchisee_code
    ,franchisee_name
from jms_dim.dim_sys_network_detail_dt
where dt = '{{ execution_date | cst_ds }}' and is_delete = 1 and is_enable = 1
),
t_region as (
select
    *
from jms_dim.dim_sys_manage_region where is_delete = 1
),

t_shipment as (
select
    shipment_no
from jms_dwd.dwd_tmsnew_shipment_union_base_dt
where dt = '{{ execution_date | cst_ds }}' and transport_type = 'GX' and table_type = 1
group by shipment_no
)

insert overwrite table jms_dm.dm_site_digital_real_detail_dt partition(dt)
select *
from
(select
    date_format(todeliver_time, 'yyyy-MM-dd') todeliver_date,
    regional_id,
    provider_id,
    agent_code,
    franchisee_code,
    this_station_code as network_code,
    flight_type,
    billcode,
    todeliver_time,
    case when flight_type = '2' and network_type = '1' then '1'
    when flight_type = '2' and network_type = '2' and last_station_type = '1' then if(belong_to = 1,'2','1')
    when flight_type = '2' and network_type = '2' and last_station_type = '2' and next_station_type = '1' then '1'
    when flight_type = '2' and network_type = '2' and last_station_type = '2' and next_station_type = '3' then '2'
    when flight_type = '2' and network_type = '2' and last_station_type = '3' then '1'
    when flight_type = '2' and network_type = '3' and last_station_type = '3' and next_station_type = '3' then '3'
    when flight_type = '2' and network_type = '3' and last_station_type = '3' and next_station_type is not null then '1'
    when flight_type = '2' and network_type = '3' and last_station_type != '3' then if(is_gx = 1,'1','2')
    when flight_type = '1' and network_type = '1' then '2'
    when flight_type = '1' and network_type = '2' and next_station_type = '1' then '1'
    when flight_type = '1' and network_type = '2' and next_station_type = '2' and last_station_type = '1' then '2'
    when flight_type = '1' and network_type = '2' and next_station_type = '2' and last_station_type = '3' then '1'
    when flight_type = '1' and network_type = '2' and next_station_type = '3' then '2'
    when flight_type = '1' and network_type = '3' then if(next_station_type = '3','3','1')
    end as departure_type,

    regional_desc,
    provider_desc,
    agent_name,
    franchisee_name,
    network_type,
    network_name,
    shipment_no,
    if(flight_type = '1',next_station_code,last_station_code) last_next_station_code,
    if(flight_type = '1',next_station_name,last_station_name) last_next_station_name,
    if(flight_type = '1',next_station_type,last_station_type) last_next_station_type,
    date_format(todeliver_time, 'yyyy-MM-dd') as dt
from
(
select
    billcode,
    t_region.code as regional_id,
    t_region.name as regional_desc,
    t_network_final.provider_id,
    t_network_final.provider_desc,
    t_network_final.agent_code,
    t_network_final.agent_name,
    t_network_final.franchisee_code,
    t_network_final.franchisee_name,
    this_station_code,
    t_network_final.name as network_name,
    case when t_network_final.type_id=336 and t_network_final.is_entrepot=2 and t_network_final.is_distribution_center=2 then '1'
    when t_network_final.is_entrepot= 1 then '2'
    when t_network_final.is_distribution_center = 1 then '3' end network_type,

    flight_type,
    todeliver_time,
    next_station_code,
    t2.name as next_station_name,
    case when t2.type_id=336 and t2.is_entrepot=2 and t2.is_distribution_center=2 then '1'
    when t2.is_entrepot= 1 then '2'
    when t2.is_distribution_center = 1 then '3' end next_station_type,

    last_station_code,
    t3.name as last_station_name,
    t3.id,
    case when t3.type_id=336 and t3.is_entrepot=2 and t3.is_distribution_center=2 then '1'
    when t3.is_entrepot= 1 then '2'
    when t3.is_distribution_center = 1 then '3' end last_station_type,

    if(t4.code is not null ,1,0) belong_to,
    t1.shipment_no,
    if(t5.shipment_no is not null ,1,0) is_gx
from
(
select
    --网点到件
    waybill_no as billcode,
    network_code as this_station_code,
    arrival_early_scantime as todeliver_time,
    "2" flight_type,
    loading_send_next_network_code as next_station_code,
    prev_network_code as last_station_code,
    unloading_arrival_shipment_no as shipment_no
from t_route
where arrival_early_scantime is not null and to_date(arrival_early_scantime) >= date_add('{{ execution_date | cst_ds }}',-3) and step != 1

union all

select
    --网点发件
    waybill_no as billcode,
    network_code as this_station_code,
    send_later_scantime as todeliver_time,
    "1" flight_type,
    loading_send_next_network_code as next_station_code,
    prev_network_code as last_station_code,
    loading_send_shipment_no as shipment_no
from t_route
where send_later_scantime is not null and loading_send_next_network_code is not null and loading_send_next_network_code != '' and to_date(send_later_scantime) >= date_add('{{ execution_date | cst_ds }}',-3)
) t1
left join t_network_final on t1.this_station_code = t_network_final.code
left join t_network_final t2 on t1.next_station_code = t2.code
left join t_network_final t3 on t1.last_station_code = t3.code
left join t_region on t_network_final.agent_id = t_region.proxy_network_id
left join jms_dim.dim_lmdm_sys_network_distributi t4 on t3.id = t4.network_id and t4.distribution_type = 3 and t4.code = this_station_code
left join t_shipment t5 on t1.shipment_no = t5.shipment_no
) t6
) t7
DISTRIBUTE BY abs(hash(billcode)) % 100 ;